package com.example.springbootecharts.controller;


import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class Tabpanelcontroller {

    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/tabpanel/companysize_money/{companysize}")
    public String tabpanelecharts1(@PathVariable String companysize) {
        String sql = " SELECT c.companysize,ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM companytypesize c RIGHT JOIN jobnamemoney m on c.id = m.id WHERE c.job_name LIKE '%"+companysize+"%' GROUP BY c.companysize;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/tabpanel/companysize_num/{companysize}")
    public String tabpanelecharts2(@PathVariable String companysize) {
        String sql = "SELECT companysize AS name, COUNT(companysize) AS value FROM companytypesize WHERE job_name LIKE '%"+companysize+"%' GROUP BY companysize ;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/tabpanel/companytype_money/{companytype}")
    public String tabpanelecharts3(@PathVariable String companytype) {
        String sql = "SELECT c.companytype,ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM companytypesize c RIGHT JOIN jobnamemoney m on c.id = m.id WHERE c.job_name LIKE '%"+companytype+"%' GROUP BY c.companytype;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/tabpanel/companytype_num/{companytype}")
    public String tabpanelecharts4(@PathVariable String companytype) {
        String sql = "SELECT companytype AS name, COUNT(companytype) AS value FROM companytypesize WHERE job_name LIKE '%"+companytype+"%' GROUP BY companytype;\n";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }
}
